package com.example.demo;

import com.sun.rowset.internal.Row;
import javafx.scene.control.Cell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.junit.Test;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

/**
 * @Classname XlsxUtil
 * @Description TODO
 * @Date 2021/7/13 22:16
 * @Created by 17976
 */
public class XlsxUtil {

    @Test
    public void test() throws IOException {
        List<Po> list = new ArrayList<>();
        String filePath = "C:\\Users\\17976\\Desktop\\XX发电站.xlsx";
        FileInputStream inputStream = new FileInputStream(filePath);
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        XSSFSheet sheet1 = workbook.getSheetAt(0);
        //开始行
        int firstRowNum = sheet1.getFirstRowNum();
        XSSFRow row = sheet1.getRow(firstRowNum);
        short firstCellNum = row.getFirstCellNum();
        //根节点
        XSSFCell root = row.getCell(firstCellNum);
        read(sheet1,root,list,new Po("0", "", ""));
        System.out.println(list);
        return;
    }

    public void read(XSSFSheet sheet1, XSSFCell child, List<Po> list, Po parentNode) {
        Po po = new Po(getUUID32(), parentNode.getId(), child.getStringCellValue());
        list.add(po);
        //判断有没有子节点
        if (sheet1.getRow(child.getRowIndex()).getCell(child.getColumnIndex() + 1)!=null) {
            CellRangeAddress mergedRegion = isMergedRegion(sheet1, child);
            //判断节点是否是合并单元格
            if (mergedRegion != null) {
                //是，则遍历其所有子节点
                for (int i = mergedRegion.getFirstRow(); i <= mergedRegion.getLastRow(); i++) {
                    XSSFCell cell = sheet1.getRow(i).getCell(child.getColumnIndex() + 1);
                    if (cell != null) {
                        CellRangeAddress ranger = isMergedRegion(sheet1, cell);
                        //若子节点也是合并节点，就要将i移动到下一个子节点前
                        if (ranger != null) {
                            i = ranger.getLastRow();
                        }
                        //读取子节点的所有子子节点
                        read(sheet1, cell, list, po);
                    }
                }
            } else {
                //否，直接read节点
                read(sheet1, sheet1.getRow(child.getRowIndex()).getCell(child.getColumnIndex() + 1), list, po);
            }
        }
    }

    public CellRangeAddress isMergedRegion(Sheet sheet, XSSFCell cell) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            if (range.isInRange(cell)) {
                return range;
            }
        }
        return null;
    }

    public static String getUUID32() {
        return UUID.randomUUID().toString().replace("-", "").toLowerCase();
    }
}
